In a typical relational database management system (DBMS), data is stored within multiple databases, with each database containing one or more tables and each table containing one or more columns. Data for each of these columns are added to individual rows so that the layout of a table is essentially a grid formation with horizontal rows and vertical columns. A row holds information on one of the entities stored in the table and the columns contain attributes of that entity. By way of example, an organization may have an employee table with columns representing employee serial number, name, work location, phone number, home contact details, salary and so forth. Each row in that table would hold information for a single employee. One of the primary use cases for databases is the selection of data from the database for eventual consumption by end users or programs. In relational databases, this is typically handled by a potentially complex command called SELECT. This is one of the commands made available in the standard Structured Query Language (SQL). The SELECT statement allows the extraction of data with many clauses dictating characteristics such as which data is extracted from each row (which columns), how the rows are filtered (which rows), how rows may be aggregated (such as rolling up employees from a single region into a single row to give the total or average salary for the region), how the rows are ordered (such as by employee serial number or last name), as well as many other options, including complex options that join different tables together to get useful information. In traditional database implementations, it is the first option, the selection of what columns to extract, which is the most limiting.
In the SQL standard, and in the vast majority of DBMS products that implement SQL, there are essentially two choices when selecting columns. First, selecting all the columns with SELECT *, or second, selecting specific, known-in-advance columns, such as SELECT employee_id, UPPER(employee_name), employee_address. The use of the SELECT * is often frowned upon since it may involve retrieving data from the database that is not actually needed. In addition, SELECT * has the limitation that the user cannot perform transformation functions on the column data (such as UPPER( ) to return uppercase values for one of the columns). To select less than the full set of columns, or to use transformation functions, the user is faced with using the second form that specifies each of the columns being retrieved. A primary challenge with using the second form is that it often requires rather large select statements since the user needs to specify every column that is being requested, as well as specifying each and every function performed on those columns. This can lead to complex, ungainly SELECT statements which are time consuming and difficult to construct.